package com.example.qxfw.qxzs.mapper;

import com.baomidou.dynamic.datasource.annotation.DS;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.qxfw.common.entity.zy.Zhishu;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Mapper;


import java.sql.Timestamp;
import java.util.List;

@Mapper
@DS("service")
public interface ZhishuMapper extends BaseMapper<Zhishu> {

    @Select("select cityid " +
            "from zhishu " +
            "where  cityname like '${cityName}%' and datechar = '${dataChar}' and timechar='${timeChar}' ")
    List<String> selectCityId(String cityName,String dateChar,String timeChar);

    @Select("select CityID, CityName, to_char(DateChar,'yyyy-mm-dd') as datechar, TimeChar, Longitude, " +
            "Latitude,  FSGrade|| ':'|| FSText as fsgrade,  MBGrade|| ':' || MBText as mbgrade, " +
            "ZZGrade|| ':' || ZZText as zzgrade, LSGrade|| ':' ||LSText as lsgrade,  " +
            "LYGrade||':'|| LYText as lygrade,  RDGrade|| ':' || RDText as rdgrade, SSGrade|| ':' ||SSText as ssgrade, " +
            "ZWGrade|| ':' || ZWText as zwgrade, PJGrade||':' || PJText as pjgrade, KTGrade|| ':' || KTText as ktgrade, " +
            "TGGrade|| ':' || TGText as tggrade,  YSGrade|| ':' || YSText as ysgrade,  WRGrade|| ':' || WRText as wrgrade, " +
            "BYXGrade|| ':' || BYXText as byxgrade,  ZSGrade|| ':' || ZSText as zsgrade, " +
            " GMGrade|| ':' || GMText as gmgrade, FYGrade|| ':' ||FYText as fygrade,  " +
            "SHXGrade|| ':' || SHXText as shxgrade, XHXGrade|| ':' || XHXText as xhxgrade, " +
            "XCGrade|| ':' || XCText as xcgrade, NYXGrade|| ':' ||NYXText as nyxgrade, " +
            "NGSGrade|| ':' || NGSText as ngsgrade, GXBGrade|| ':' || GXBText as gxbgrade, " +
            " XJGSGrade|| ':' || XJGSText as xjgsgrade, GXYGrade||':' || GXYText as gxygrade, " +
            " LYOUGrade|| ':' || LYOUText as lyougrade, CDGrade|| ':' || CDText as cdgrade, " +
            "JTGrade|| ':' || JTText as jtgrade,  YDGrade|| ':' || YDText as ydgrade, " +
            "HXGrade|| ':' ||HXText as hxgrade, CLGrade|| ':' ||CLText as clgrade " +
            "from zhishu " +
            "where cityid in ('${cityId}') and datechar='${dateChar}' and timechar='${timeChar}' ")
    List<Zhishu> selectList1(String cityId, Timestamp dateChar, String timeChar);

    @Select("select DateChar, TimeChar " +
            "from zhishu  " +
            "order by DateChar desc " +
            "limit 1")
    List<Zhishu> selectMaxTime();

    //"where cityid like '%5' " +
    @Select("select CityID, CityName, to_char(DateChar,'yyyy-mm-dd') as datechar, TimeChar, Longitude, " +
            "Latitude,  FSGrade|| ':'|| FSText as fsgrade,  MBGrade|| ':' || MBText as mbgrade, " +
            "ZZGrade|| ':' || ZZText as zzgrade, LSGrade|| ':' ||LSText as lsgrade,  " +
            "LYGrade||':'|| LYText as lygrade,  RDGrade|| ':' || RDText as rdgrade, SSGrade|| ':' ||SSText as ssgrade, " +
            "ZWGrade|| ':' || ZWText as zwgrade, PJGrade||':' || PJText as pjgrade, KTGrade|| ':' || KTText as ktgrade, " +
            "TGGrade|| ':' || TGText as tggrade,  YSGrade|| ':' || YSText as ysgrade,  WRGrade|| ':' || WRText as wrgrade, " +
            "BYXGrade|| ':' || BYXText as byxgrade,  ZSGrade|| ':' || ZSText as zsgrade, " +
            " GMGrade|| ':' || GMText as gmgrade, FYGrade|| ':' ||FYText as fygrade,  " +
            "SHXGrade|| ':' || SHXText as shxgrade, XHXGrade|| ':' || XHXText as xhxgrade, " +
            "XCGrade|| ':' || XCText as xcgrade, NYXGrade|| ':' ||NYXText as nyxgrade, " +
            "NGSGrade|| ':' || NGSText as ngsgrade, GXBGrade|| ':' || GXBText as gxbgrade, " +
            " XJGSGrade|| ':' || XJGSText as xjgsgrade, GXYGrade||':' || GXYText as gxygrade, " +
            " LYOUGrade|| ':' || LYOUText as lyougrade, CDGrade|| ':' || CDText as cdgrade, " +
            "JTGrade|| ':' || JTText as jtgrade,  YDGrade|| ':' || YDText as ydgrade, " +
            "HXGrade|| ':' ||HXText as hxgrade, CLGrade|| ':' ||CLText as clgrade " +
            "from zhishu " +
            "order by DateChar desc")
    List<Zhishu> selectList2();

    @Select("select CityID, CityName, to_char(DateChar,'yyyy-mm-dd') as datechar, TimeChar, Longitude, " +
            "Latitude,  FSGrade|| ':'|| FSText as fsgrade,  MBGrade|| ':' || MBText as mbgrade, " +
            "ZZGrade|| ':' || ZZText as zzgrade, LSGrade|| ':' ||LSText as lsgrade,  " +
            "LYGrade||':'|| LYText as lygrade,  RDGrade|| ':' || RDText as rdgrade, SSGrade|| ':' ||SSText as ssgrade, " +
            "ZWGrade|| ':' || ZWText as zwgrade, PJGrade||':' || PJText as pjgrade, KTGrade|| ':' || KTText as ktgrade, " +
            "TGGrade|| ':' || TGText as tggrade,  YSGrade|| ':' || YSText as ysgrade,  WRGrade|| ':' || WRText as wrgrade, " +
            "BYXGrade|| ':' || BYXText as byxgrade,  ZSGrade|| ':' || ZSText as zsgrade, " +
            " GMGrade|| ':' || GMText as gmgrade, FYGrade|| ':' ||FYText as fygrade,  " +
            "SHXGrade|| ':' || SHXText as shxgrade, XHXGrade|| ':' || XHXText as xhxgrade, " +
            "XCGrade|| ':' || XCText as xcgrade, NYXGrade|| ':' ||NYXText as nyxgrade, " +
            "NGSGrade|| ':' || NGSText as ngsgrade, GXBGrade|| ':' || GXBText as gxbgrade, " +
            " XJGSGrade|| ':' || XJGSText as xjgsgrade, GXYGrade||':' || GXYText as gxygrade, " +
            " LYOUGrade|| ':' || LYOUText as lyougrade, CDGrade|| ':' || CDText as cdgrade, " +
            "JTGrade|| ':' || JTText as jtgrade,  YDGrade|| ':' || YDText as ydgrade, " +
            "HXGrade|| ':' ||HXText as hxgrade, CLGrade|| ':' ||CLText as clgrade " +
            "from zhishu " +
            "where 1=1 ${sql} " +
            "order by dateCharDesc ")
    List<Zhishu> selectList3(String sql);

    @Select("select CityID, CityName, to_char(DateChar,'yyyy-mm-dd') as datechar, TimeChar, Longitude, " +
            "Latitude,  FSGrade|| ':'|| FSText as fsgrade,  MBGrade|| ':' || MBText as mbgrade, " +
            "ZZGrade|| ':' || ZZText as zzgrade, LSGrade|| ':' ||LSText as lsgrade,  " +
            "LYGrade||':'|| LYText as lygrade,  RDGrade|| ':' || RDText as rdgrade, SSGrade|| ':' ||SSText as ssgrade, " +
            "ZWGrade|| ':' || ZWText as zwgrade, PJGrade||':' || PJText as pjgrade, KTGrade|| ':' || KTText as ktgrade, " +
            "TGGrade|| ':' || TGText as tggrade,  YSGrade|| ':' || YSText as ysgrade,  WRGrade|| ':' || WRText as wrgrade, " +
            "BYXGrade|| ':' || BYXText as byxgrade,  ZSGrade|| ':' || ZSText as zsgrade, " +
            " GMGrade|| ':' || GMText as gmgrade, FYGrade|| ':' ||FYText as fygrade,  " +
            "SHXGrade|| ':' || SHXText as shxgrade, XHXGrade|| ':' || XHXText as xhxgrade, " +
            "XCGrade|| ':' || XCText as xcgrade, NYXGrade|| ':' ||NYXText as nyxgrade, " +
            "NGSGrade|| ':' || NGSText as ngsgrade, GXBGrade|| ':' || GXBText as gxbgrade, " +
            " XJGSGrade|| ':' || XJGSText as xjgsgrade, GXYGrade||':' || GXYText as gxygrade, " +
            " LYOUGrade|| ':' || LYOUText as lyougrade, CDGrade|| ':' || CDText as cdgrade, " +
            "JTGrade|| ':' || JTText as jtgrade,  YDGrade|| ':' || YDText as ydgrade, " +
            "HXGrade|| ':' ||HXText as hxgrade, CLGrade|| ':' ||CLText as clgrade " +
            "from zhishu " +
            "where  cityid in ('${cityId}')) and (((DateChar = '${dateChar}') AND (TimeChar >= '${timeChar}')) OR ((DateChar = '${dateChar1}' AND TimeChar <= '${timeChar1}')) OR (DateChar > '${dateChar}' AND DateChar < '${dateChar}')) ")
    List<Zhishu> selectList4(String cityId, Timestamp dateChar,Timestamp dateChar1, String timeChar,String timeChar1);


    @Select("select CityID, CityName, to_char(DateChar,'yyyy-mm-dd') as datechar, TimeChar, Longitude, " +
            "Latitude,  FSGrade|| ':'|| FSText as fsgrade,  MBGrade|| ':' || MBText as mbgrade, " +
            "ZZGrade|| ':' || ZZText as zzgrade, LSGrade|| ':' ||LSText as lsgrade,  " +
            "LYGrade||':'|| LYText as lygrade,  RDGrade|| ':' || RDText as rdgrade, SSGrade|| ':' ||SSText as ssgrade, " +
            "ZWGrade|| ':' || ZWText as zwgrade, PJGrade||':' || PJText as pjgrade, KTGrade|| ':' || KTText as ktgrade, " +
            "TGGrade|| ':' || TGText as tggrade,  YSGrade|| ':' || YSText as ysgrade,  WRGrade|| ':' || WRText as wrgrade, " +
            "BYXGrade|| ':' || BYXText as byxgrade,  ZSGrade|| ':' || ZSText as zsgrade, " +
            " GMGrade|| ':' || GMText as gmgrade, FYGrade|| ':' ||FYText as fygrade,  " +
            "SHXGrade|| ':' || SHXText as shxgrade, XHXGrade|| ':' || XHXText as xhxgrade, " +
            "XCGrade|| ':' || XCText as xcgrade, NYXGrade|| ':' ||NYXText as nyxgrade, " +
            "NGSGrade|| ':' || NGSText as ngsgrade, GXBGrade|| ':' || GXBText as gxbgrade, " +
            " XJGSGrade|| ':' || XJGSText as xjgsgrade, GXYGrade||':' || GXYText as gxygrade, " +
            " LYOUGrade|| ':' || LYOUText as lyougrade, CDGrade|| ':' || CDText as cdgrade, " +
            "JTGrade|| ':' || JTText as jtgrade,  YDGrade|| ':' || YDText as ydgrade, " +
            "HXGrade|| ':' ||HXText as hxgrade, CLGrade|| ':' ||CLText as clgrade " +
            "from zhishu " +
            "where datechar='${dataChar}' and timechar='${timeChar}' ")
    List<Zhishu> selectList5(Timestamp dateChar, String timeChar);

    @Select("select CityID, CityName, to_char(DateChar,'yyyy-mm-dd') as datechar, TimeChar, Longitude, " +
            "Latitude,  FSGrade|| ':'|| FSText as fsgrade,  MBGrade|| ':' || MBText as mbgrade, " +
            "ZZGrade|| ':' || ZZText as zzgrade, LSGrade|| ':' ||LSText as lsgrade,  " +
            "LYGrade||':'|| LYText as lygrade,  RDGrade|| ':' || RDText as rdgrade, SSGrade|| ':' ||SSText as ssgrade, " +
            "ZWGrade|| ':' || ZWText as zwgrade, PJGrade||':' || PJText as pjgrade, KTGrade|| ':' || KTText as ktgrade, " +
            "TGGrade|| ':' || TGText as tggrade,  YSGrade|| ':' || YSText as ysgrade,  WRGrade|| ':' || WRText as wrgrade, " +
            "BYXGrade|| ':' || BYXText as byxgrade,  ZSGrade|| ':' || ZSText as zsgrade, " +
            " GMGrade|| ':' || GMText as gmgrade, FYGrade|| ':' ||FYText as fygrade,  " +
            "SHXGrade|| ':' || SHXText as shxgrade, XHXGrade|| ':' || XHXText as xhxgrade, " +
            "XCGrade|| ':' || XCText as xcgrade, NYXGrade|| ':' ||NYXText as nyxgrade, " +
            "NGSGrade|| ':' || NGSText as ngsgrade, GXBGrade|| ':' || GXBText as gxbgrade, " +
            " XJGSGrade|| ':' || XJGSText as xjgsgrade, GXYGrade||':' || GXYText as gxygrade, " +
            " LYOUGrade|| ':' || LYOUText as lyougrade, CDGrade|| ':' || CDText as cdgrade, " +
            "JTGrade|| ':' || JTText as jtgrade,  YDGrade|| ':' || YDText as ydgrade, " +
            "HXGrade|| ':' ||HXText as hxgrade, CLGrade|| ':' ||CLText as clgrade " +
            "from zhishu " +
            "where (((DateChar = '${dateChar}') AND (TimeChar >= '${timeChar}')) OR ((DateChar = '${dateChar1}' AND TimeChar <= '${timeChar1}')) OR (DateChar > '${dateChar}' AND DateChar < '${dateChar}')) ")
    List<Zhishu> selectList6(Timestamp dateChar,Timestamp dateChar1, String timeChar,String timeChar1);

}
